#include "databaseoperate.h"
#include <QDateTime>
#include <QMutex>
#include <QSettings>
#include <QSqlError>
databaseOperate *databaseOperate::self = 0;
databaseOperate *databaseOperate::Instance()
{
    if (!self) {
            QMutex mutex;
            QMutexLocker locker(&mutex);
            if (!self) {
                self = new databaseOperate;
            }
        }

        return self;
}
databaseOperate::databaseOperate()
{
    readIni();
    opendb();
}
databaseOperate::~databaseOperate()
{
    closedb();
}
void databaseOperate::opendb()
{
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("localhost");
    db.setPort(3306);
    db.setDatabaseName("czt100");
    db.setUserName("root");
    db.setPassword(sqlValue);
    bool ok = db.open();
    if(!ok){
        QMessageBox::warning(NULL, "警告", "数据库连接失败,请联系管理员！");
    }
}
void databaseOperate::readIni()
{
    QString path = "set.ini";
    QSettings *config = new QSettings(path, QSettings::IniFormat);
    //读取配置信息
    sqlValue = config->value(QString("sql/") + "sqlValue").toString();

}
void databaseOperate::closedb()
{
    db.close();
}
void databaseOperate::querySample(QMap<int, QString> &list)
{
    QSqlQuery query("select distinct a.SAMPLE_INST_NAME,a.SAMPLE_INST_ID from sample_inst a where a.STATUS_CD = 1000");
    while(query.next())
    {
        list.insert(query.value(1).toInt(), query.value(0).toString());
    }
}
/**
 * @brief databaseOperate::querySampleAttr
 * @param inst_id
 * @param list
 * @details 获取属性值
 */
void databaseOperate::querySampleAttr(const int inst_id, QMap<int, QString> &list)
{
    QSqlQuery query("select a.attr_id,a.attr_value from sample_inst_attr a where a.SAMPLE_INST_ID = " + QString::number(inst_id));
    while(query.next())
    {
        list.insert(query.value(0).toInt(), query.value(1).toString());
    }
}
/**
 * @brief databaseOperate::addSample
 * 新增样品实例
 */
void databaseOperate::addSample(QString samInsName, QMap<int, QString> tMap)
{
    QSqlQuery query;
    query.prepare("INSERT INTO SAMPLE_INST(sample_inst_id, sample_inst_name, sample_inst_desc, status_cd, create_date)values\
    (null, :sample_inst_name, '', '100', current_timestamp)");
    query.bindValue(":sample_inst_name", samInsName);
    bool ok = query.exec();
    if(ok)//  新增实例成功,获取该实例ID,插入对应实例属性 3步
    {
        QSqlQuery query1("select Max(a.sample_inst_id) from sample_inst a");
        while(query1.next())// 获取插入的样品实例
        {
            int max_inst_id = query1.value(0).toInt();// 样品实例ID
            addSampleAttr(tMap, max_inst_id);
        }
    }
    else//  新增实例失败
    {
        QMessageBox::information(NULL, "提示", "新增失败！", QMessageBox::Yes , QMessageBox::Yes);
        return ;
    }
}

/**
 * @brief databaseOperate::addSampleAttr
 * 新增样品属性信息
 * @param list
 */
void databaseOperate::addSampleAttr(QMap<int, QString> tMap, int maxInstId)
{
    QSqlQuery query;
    QMapIterator<int, QString> i(tMap);
    while(i.hasNext())
    {
        i.next();
        if(i.value() == "")
            continue;
        query.prepare("insert into sample_inst_attr(sample_inst_attr_id, sample_inst_id, attr_id, attr_value, update_staff, create_date, update_date)\
                         values(null, :sample_inst_id, :attr_id, :attr_value, :update_staff, current_timestamp, current_timestamp);");
        query.bindValue(":sample_inst_id", maxInstId);
        query.bindValue(":attr_id", i.key());
        query.bindValue(":attr_value", i.value());
        query.bindValue("update_staff", "");
        if(!query.exec())
        {
            QMessageBox::information(NULL, "提示", "插入样品属性实例失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
            return;
        }
    }
    QMessageBox::information(NULL, "提示", "新增成功！", QMessageBox::Yes , QMessageBox::Yes);
}
void databaseOperate::addTestAttrInfo(QMap<int, QString> tMap, int)
{
    QSqlQuery query;
    query.prepare("insert into test_inst_attr(test_inst_attr_id, strength, compressure, diameter, xingbian, tanxing, shifts, surveyor, create_date)\
                    values(null, :strength, :compressure, :diameter, :xingbian, :tanxing, :shifts, :surveyor, current_timestamp);");
    query.bindValue(":strength", tMap.value(300));
    query.bindValue(":compressure", tMap.value(301));
    query.bindValue(":diameter", tMap.value(302));
    query.bindValue(":xingbian", tMap.value(303));
    query.bindValue(":tanxing", tMap.value(304));
    query.bindValue(":shifts", tMap.value(305));
    query.bindValue(":surveyor", tMap.value(306));
    if(!query.exec())
    {
        QMessageBox::information(NULL, "提示", "测试数据保存失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
        return;
    }
}
void databaseOperate::addTestInfo(QList<TestInfo> listInfo, QString str1, QString str2, QString str3, QString str4, QString str5)
{

    for(int i = 0; i < listInfo.count(); i++)
    {
        QSqlQuery query;
        query.prepare("insert into test_inst(testId, testflagId, testNumber, sample_inst_name, sample_inst_id, openData, closeData, testDate, surveyor, shifts, status_cd)\
        values(null, :testflagId, :testNumber, :sample_inst_name, :sample_inst_id, :openData, :closeData, current_timestamp, :surveyor, :shifts, :status_cd);");
        query.bindValue(":testflagId", str5);
        query.bindValue(":testNumber", listInfo.at(i).num);
        query.bindValue(":sample_inst_name", str3);
        query.bindValue(":sample_inst_id", str4);
        query.bindValue(":openData", listInfo.at(i).open);
        query.bindValue(":closeData", listInfo.at(i).close);
        query.bindValue(":surveyor", str1);
        query.bindValue(":shifts", str2);
        query.bindValue(":status_cd", 1000);
        if(!query.exec())
        {
            QMessageBox::information(NULL, "提示", "测试数据保存失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
            return;
        }
    }
}
/**
 * @brief databaseOperate::delSampleInfo
 * 删除样品信息
 * @param selInstId
 * @return
 */
bool databaseOperate::delSampleInfo(int selInstId)
{
    QSqlQuery query;
    query.prepare("delete from sample_inst where sample_inst_id = :sample_inst_id");
    query.bindValue(":sample_inst_id", selInstId);
    if(!query.exec())
    {
        QMessageBox::information(NULL, "提示", "删除样品实例失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
        return false;
    }
    query.prepare("delete from sample_inst_attr where sample_inst_id = :sample_inst_id");
    query.bindValue(":sample_inst_id", selInstId);
    if(!query.exec())
    {
        QMessageBox::information(NULL, "提示", "删除样品属性实例失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
        return false;
    }
    return true;
}
/**
 * @brief databaseOperate::editSampleInfo
 * 编辑样品信息
 * @param tMap
 * @param editInstId
 */
void databaseOperate::editSampleInfo(QMap<int, QString> tMap, int editInstId)
{
    QSqlQuery query;
    query.prepare("update sample_inst a set a.sample_inst_name = :sample_inst_name where a.sample_inst_id \
    = :sample_inst_id ");
    query.bindValue(":sample_inst_name", tMap.value(100)); qDebug()<<"sample_name:"<<tMap.value(100);
    query.bindValue(":sample_inst_id", editInstId);
    if(!query.exec())
    {
        QMessageBox::information(NULL, "提示", "修改样品实例失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
        return;
    }

    QMapIterator<int, QString> i(tMap);
    while(i.hasNext())
    {
        i.next();
        query.prepare("update sample_inst_attr a set a.attr_value = :attr_value where a.sample_inst_id = :sample_inst_id and a.attr_id = :attr_id");
        query.bindValue(":attr_value", i.value());
        query.bindValue(":sample_inst_id", editInstId);
        query.bindValue(":attr_id", i.key());
        if(!query.exec())
        {
            QMessageBox::information(NULL, "提示", "修改样品实例失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
            return;
        }
    }

    QMessageBox::information(NULL, "提示", "修改样品成功！", QMessageBox::Yes , QMessageBox::Yes);
    return;

}

void databaseOperate::queryShifts(QMap<int, QString> &list)
{
    QSqlQuery query("select a.shift_id,a.shift_name from shifts a");
    while(query.next())
    {
        list.insert(query.value(0).toInt(), query.value(1).toString());
    }
}

void databaseOperate::queryTestShiftInfo(QList<QString> &list)
{
    QSqlQuery query("select DISTINCT t.shifts from test_inst t ");
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
}
void databaseOperate::queryTestSurveyorInfo(QList<QString> &list)
{
    QSqlQuery query("select DISTINCT t.surveyor from test_inst t ");
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
}
void databaseOperate::queryTestSampleInfo(QList<QString> &list)
{
    QSqlQuery query("select DISTINCT t.sample_inst_name from test_inst t ");
    while(query.next())
    {
        list.append(query.value(0).toString());
    }
}
void databaseOperate::querySurveyor(QMap<int, QString> &list)
{
    QSqlQuery query("select a.surveyor_id,a.surveyor_name from surveyor a");
    while(query.next())
    {
        list.insert(query.value(0).toInt(), query.value(1).toString());
    }
}

void databaseOperate::queryTestInstAttr(TestAttrList &c)
{
    QSqlQuery query("select t.TEST_INST_ATTR_ID, t.CREATE_DATE, t.STRENGTH, t.COMPRESSURE, t.DIAMETER, t.XINGBIAN, t.TANXING, t.SHIFTS, t.SURVEYOR from test_inst_attr t ;");
    while (query.next()) {
        TestAttr tp;
        tp.append(query.value(0).toString());
        tp.append(query.value(1).toDateTime().toString("yyyy-MM-dd hh:mm:ss"));  //
        tp.append(query.value(2).toString());
        tp.append(query.value(3).toString());
        tp.append(query.value(4).toString());
        tp.append(query.value(5).toString());
        tp.append(query.value(6).toString());
        tp.append(query.value(7).toString());
        tp.append(query.value(8).toString());
        c.append(tp);
    }
}
void databaseOperate::queryTestInfo(TestAttrList &c)
{
    QSqlQuery query("select t.* from test_inst t ;");
    while(query.next())
    {
        TestAttr tp;
//        tp.append(query.value(0).toString());
        tp.append(query.value(1).toString());
        tp.append(query.value(2).toString());
        tp.append(query.value(3).toString());
        tp.append(query.value(4).toString());
        tp.append(query.value(5).toString());
        tp.append(query.value(6).toString());
        tp.append(query.value(7).toDateTime().toString("yyyy-MM-dd"));
        tp.append(query.value(8).toString());
        tp.append(query.value(9).toString());
//        tp.append(query.value(10).toString());
        c.append(tp);
    }
}
void databaseOperate::queryCondition(Condition cond, TestAttrList &c)
{
    QString sqlText = "select t.* from test_inst t ";
    if(cond.band != "" || cond.date!= "" || cond.shifts != "" || cond.suveyor != "" || cond.number != "")
    {
        sqlText += " where 1=1 ";
    }
    if(cond.band != "")
        sqlText = sqlText +" and t.sample_inst_name = '"+cond.band + "'";
    if(cond.date != "")
        sqlText = sqlText + " and t.testDate > '" + cond.date +"'";
    if(cond.shifts != "")
        sqlText = sqlText + " and t.shifts = '" + cond.shifts +"'";
    if(cond.suveyor != "")
        sqlText = sqlText + " and t.surveyor = '" + cond.suveyor +"'";
    if(cond.number != "")
        sqlText = sqlText + " and t.testflagId = '" + cond.number +"'";
    QSqlQuery query(sqlText);
    while (query.next()) {
        TestAttr tp;
        tp.append(query.value(0).toString());
        tp.append(query.value(1).toString());
        tp.append(query.value(2).toString());
        tp.append(query.value(3).toString());
        tp.append(query.value(4).toString());
        tp.append(query.value(5).toString());
        tp.append(query.value(6).toString());
        tp.append(query.value(7).toDateTime().toString("yyyy-MM-dd"));
        tp.append(query.value(8).toString());
        tp.append(query.value(9).toString());
//        tp.append(query.value(10).toString());
        c.append(tp);
    }
}
void databaseOperate::queryPortIni(QString &str, int t_id)
{
    QSqlQuery query("select portName from portini where remark = '吸阻仪'");
    while(query.next())
    {
        str = query.value(0).toString();
    }
}
void databaseOperate::addSample(QString name)
{
    QSqlQuery query;
    query.prepare("INSERT INTO SAMPLE_INST(sample_inst_id, sample_inst_name, sample_inst_desc, status_cd, create_date)values\
    (null, :sample_inst_name, '', '1000', current_timestamp)");
    query.bindValue(":sample_inst_name", name);
    bool ok = query.exec();
    if(!ok)//
    {
        QMessageBox::warning(NULL, "警告", "数据新增失败,请联系管理员！");
    }
}
void databaseOperate::addShifts(QString name)
{
    QSqlQuery query;
    query.prepare("INSERT INTO shifts(shift_id, shift_name)values(null, :shift_name)");
    query.bindValue(":shift_name", name);
    bool ok = query.exec();
    if(!ok)//
    {
        QMessageBox::warning(NULL, "警告", "数据新增失败,请联系管理员！");
    }
}
void databaseOperate::addSurveyor(QString name)
{
    QSqlQuery query;
    query.prepare("INSERT INTO surveyor(surveyor_id, surveyor_name)values(null, :surveyor_name)");
    query.bindValue(":surveyor_name", name);
    bool ok = query.exec();
    if(!ok)//
    {
        QSqlError errors;
        errors = query.lastError();
        if(errors.isValid())
        {
            switch(errors.type())
            {
                case QSqlError::StatementError:
                QMessageBox::warning(NULL, "警告", "错误，请重新输入(不可重复插入)！");
                return;
            }
        }
        QMessageBox::warning(NULL, "警告", "数据新增失败,请联系管理员！");
    }
}
void databaseOperate::deleteSample(int num)
{
    QSqlQuery query;
    query.prepare("delete from sample_inst where sample_inst_id = :sample_inst_id");
    query.bindValue(":sample_inst_id", num);
    if(!query.exec())
    {
        QMessageBox::information(NULL, "提示", "删除样品实例失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
    }
}
void databaseOperate::deleteShifts(int num)
{
    QSqlQuery query;
    query.prepare("delete from shifts where shift_id = :shift_id");
    query.bindValue(":shift_id", num);
    if(!query.exec())
    {
        QMessageBox::information(NULL, "提示", "删除样品实例失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
    }
}
void databaseOperate::deleteSurveyor(int num)
{
    QSqlQuery query;
    query.prepare("delete from surveyor where surveyor_id = :surveyor_id");
    query.bindValue(":surveyor_id", num);
    if(!query.exec())
    {
        QMessageBox::information(NULL, "提示", "删除样品实例失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
    }
}
void databaseOperate::deleteTestInfo(QList<int> list)
{
    for(int i = 0; i < list.count(); i++)
    {
        QSqlQuery query;
        query.prepare("delete from test_inst where testId = :testId");
        query.bindValue(":testId", list.at(i));
        if(!query.exec())
        {
            QMessageBox::information(NULL, "提示", "删除样品实例失败,请联系管理员！", QMessageBox::Yes , QMessageBox::Yes);
            break;
        }
    }

}
